psql
is awesome!Lætitia Avrot
$ psql --host localhost --port 5432 --username laetitia \
--dbname test
psql (15devel)
Type "help" for help.
test=#
$ psql --host localhost
$ psql -h localhost
psql (15devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
on host "localhost" (address "::1") at port "5432".
$ export PGHOST=localhost
$ psql
psql (15devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
on host "localhost" (address "::1") at port "5432".
$ export PGHOST=
$ psql
psql (15devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5432".
$ psql -h /tmp
psql (15devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5432".
$ psql -h /usr/local/pgsql/data
psql: error: connection to server on socket
"/usr/local/pgsql/data/.s.PGSQL.5432"
failed: Permission denied
Is the server running locally and accepting
connections on that socket?
$ sudo -u postgres psql -h /usr/local/pgsql/data
psql: error: connection to server on socket
"/usr/local/pgsql/data/.s.PGSQL.5432"
failed: No such file or directory
Is the server running locally and accepting
connections on that socket?
laetitia=# select name, setting
laetitia-# from pg_settings
laetitia-# where name ~ 'socket';
name | setting
-------------------------+---------
unix_socket_directories | /tmp
unix_socket_group |
unix_socket_permissions | 0777
(3 rows)
$ psql --port 5433
$ psql -p 5433
psql (15devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5433".
$ export PGPORT=5433
$ psql
psql (15devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5433".
$ export PGPORT=
$ psql
psql (15devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5432".
$ psql --username test
$ psql -U test
psql (15devel)
Type "help" for help.
test=# \conninfo
You are connected to database "test" as user "test"
via socket in "/tmp" at port "5432".
$ export PGUSER=test
$ psql
psql (15devel)
Type "help" for help.
test=# \conninfo
You are connected to database "test" as user "test"
via socket in "/tmp" at port "5432".
$ export PGUSER=
$ psql
psql (15devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5432".
$ psql test
$ psql --dbname test
$ psql test
psql (15devel)
Type "help" for help.
test=# \conninfo
You are connected to database "test" as user "laetitia"
via socket in "/tmp" at port "5432".
$ export PGDATABASE=test
$ psql
psql (15devel)
Type "help" for help.
test=# \conninfo
You are connected to database "test" as user "laetitia"
via socket in "/tmp" at port "5432".
$ export PGDATABASE=
$ psql
psql (15devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia"
via socket in "/tmp" at port "5432".
$ psql postgresql://laetitia@localhost:5433/test
psql (15devel)
Type "help" for help.
test=# \conninfo
You are connected to database "test" as user "laetitia"
on host "localhost" (address "::1") at port "5433".
$ psql "user=laetitia host=localhost port=5433 dbanme=test"
psql (15devel)
Type "help" for help.
test=# \conninfo
You are connected to database "test" as user "laetitia"
on host "localhost" (address "::1") at port "5433".
$ cat ~/.pg_service.conf
[mydb]
host=localhost
# Port is same as default but I could use port=5432
user=test
dbname=laetitia
psql service=mydb
psql (15devel)
Type "help" for help.
laetitia=> \conninfo
You are connected to database "laetitia" as user "test"
on host "localhost" (address "::1") at port "5432".
export PGSERVICEFILE=~/.pg_service2.conf
$ cat ~/.pg_service2.conf
[mydb]
host=localhost
# Port is same as default but I could use port=5432
user=test
dbname=test
psql service=mydb
psql (15devel)
Type "help" for help.
laetitia=> \conninfo
You are connected to database test as user "test"
on host "localhost" (address "::1") at port "5432".
$ psql -c "select * from test;"
id | value
----+----------
1 | Use
2 | your
3 | fear...
4 | it
5 | can
6 | take
7 | you
8 | to
9 | the
10 | place
11 | where
12 | you
13 | store
14 | your
15 | courage.
(15 rows)
$ psql -c "select * from test;
select value from test where id=10;"
value
-------
place
(1 row)
$ psql -c "IDENTIFY_SYSTEM" \
-d "replication=true dbname=postgres"
systemid | timeline | xlogpos | dbname
---------------------+----------+-----------+--------
6980320202536645744 | 1 | 0/16BD170 |
(1 row)
$ cat query.sql
select * from test;
$ psql -f query.sql
id | value
----+----------
1 | Use
2 | your
3 | fear...
4 | it
5 | can
6 | take
7 | you
8 | to
9 | the
10 | place
11 | where
12 | you
13 | store
14 | your
15 | courage.
(15 rows)
$ cat query2.sql
select * from test;
select value from test where id = 10;
$ psql -f query2.sql
id | value
----+----------
1 | Use
2 | your
3 | fear...
4 | it
5 | can
6 | take
7 | you
8 | to
9 | the
10 | place
11 | where
12 | you
13 | store
14 | your
15 | courage.
(15 rows)
value
-------
place
(1 row)
$ psql "user=test dbname=laetitia"
psql (15devel)
Type "help" for help.
laetitia=>
laetitia=>
laetitia=> select
laetitia->
laetitia=> (
laetitia(>
laetitia-> '
laetitia'>
laetitia-> "
laetitia">
laetitia=> begin;
BEGIN
laetitia=*>
$ psql
psql (15devel)
Type "help" for help.
laetitia=#
laetitia=# \q
laetitia=# exit
laetitia=# help
laetitia=# \?
laetitia=# \h
\set variable value
\pset variable value
\setenv variable value
laetitia#=\set settingname wal
laetitia#=select name, setting from pg_settings
laetitia-#where name ~ :'settingname';
name | setting
-------------------------------+---------------
max_slot_wal_keep_size | -1
max_wal_senders | 10
max_wal_size | 1024
min_wal_size | 80
track_wal_io_timing | off
wal_block_size | 8192
wal_buffers | 512
wal_compression | off
wal_consistency_checking |
wal_init_zero | on
wal_keep_size | 0
wal_level | replica
wal_log_hints | off
wal_receiver_create_temp_slot | off
wal_receiver_status_interval | 10
wal_receiver_timeout | 60000
wal_recycle | on
wal_retrieve_retry_interval | 5000
wal_segment_size | 16777216
wal_sender_timeout | 60000
wal_skip_threshold | 2048
wal_sync_method | open_datasync
wal_writer_delay | 200
wal_writer_flush_after | 128
(24 rows)
laetitia:~$ cat query3.sql
select name, setting
from pg_settings
where name ~ :'settingname';
laetitia:~$ psql --variable "settingname=wal" -f query3.sql
name | setting
-------------------------------+---------------
max_slot_wal_keep_size | -1
max_wal_senders | 10
max_wal_size | 1024
min_wal_size | 80
track_wal_io_timing | off
wal_block_size | 8192
wal_buffers | 512
wal_compression | off
wal_consistency_checking |
wal_init_zero | on
wal_keep_size | 0
wal_level | replica
wal_log_hints | off
wal_receiver_create_temp_slot | off
wal_receiver_status_interval | 10
wal_receiver_timeout | 60000
wal_recycle | on
wal_retrieve_retry_interval | 5000
wal_segment_size | 16777216
wal_sender_timeout | 60000
wal_skip_threshold | 2048
wal_sync_method | open_datasync
wal_writer_delay | 200
wal_writer_flush_after | 128
(24 rows)
\pset pager off
laetitia#=\setenv PSQL_EDITOR vim
laetitia=# \l
laetitia=# \d
laetitia=# \dn
laetitia=# \dt
laetitia=# \df
laetitia=# \set ECHO_HIDDEN on
laetitia=# \dt test
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN
'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's'
THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN
'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','t','s','')
AND c.relname OPERATOR(pg_catalog.~) '^(test)$' COLLATE pg_catalog.default
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | laetitia
(1 row)
laetitia=# \x
laetitia=# \g
laetitia=# select * from pg_settings where name='max_wal_senders';
name | setting | unit | category |
short_desc |
extra_desc | context | vartype | source | min_val | max_val |
enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
-----------------+---------+------+-------------------------------+
-------------------------------------------------------------------------+
------------+------------+---------+---------+---------+---------+
----------+----------+-----------+------------+------------+-----------------
max_wal_senders | 10 | | Replication / Sending Servers |
Sets the maximum number of simultaneously running WAL sender processes. |
| postmaster | integer | default | 0 | 262143 |
| 10 | 10 | | | f
(1 row)
laetitia=# \gx
-[ RECORD 1 ]---+------------------------------------------------------------------------
name | max_wal_senders
setting | 10
unit |
category | Replication / Sending Servers
short_desc | Sets the maximum number of simultaneously running
WAL sender processes.
extra_desc |
context | postmaster
vartype | integer
source | default
min_val | 0
max_val | 262143
enumvals |
boot_val | 10
reset_val | 10
sourcefile |
sourceline |
pending_restart | f
laetitia=# \pset format html
Output format is html.
laetitia=# \g
<table border="1">
<tr>
<th align="center">name</th>
<th align="center">setting</th>
<th align="center">unit</th>
<th align="center">category</th>
<th align="center">short_desc</th>
<th align="center">extra_desc</th>
<th align="center">context</th>
<th align="center">vartype</th>
<th align="center">source</th>
<th align="center">min_val</th>
<th align="center">max_val</th>
<th align="center">enumvals</th>
<th align="center">boot_val</th>
<th align="center">reset_val</th>
<th align="center">sourcefile</th>
<th align="center">sourceline</th>
<th align="center">pending_restart</th>
</tr>
<tr valign="top">
<td align="left">max_wal_senders</td>
<td align="left">10</td>
<td align="left"> </td>
<td align="left">Replication / Sending Servers</td>
<td align="left">Sets the maximum number of simultaneously running WAL sender processes.</td>
<td align="left"> </td>
<td align="left">postmaster</td>
<td align="left">integer</td>
<td align="left">default</td>
<td align="left">0</td>
<td align="left">262143</td>
<td align="left"> </td>
<td align="left">10</td>
<td align="left">10</td>
<td align="left"> </td>
<td align="right"> </td>
<td align="left">f</td>
</tr>
</table>
<p>(1 row)<br />
</p>
laetitia=# \pset format unaligned
Output format is unaligned.
laetitia=# \g
name|setting|unit|category|short_desc|extra_desc|context|vartype|source|min_val|max_val|enumvals|boot_val|reset_val|sourcefile|sourceline|pending_restart
max_wal_senders|10||Replication / Sending Servers|Sets the maximum number of simultaneously running WAL sender processes.||postmaster|integer|default|0|262143||10|10|||f
(1 row)
laetitia=# \pset format csv
Output format is unaligned.
laetitia=# \g
name,setting,unit,category,short_desc,extra_desc,context,vartype,source,min_val,max_val,enumvals,boot_val,reset_val,sourcefile,sourceline,pending_restart
max_wal_senders,10,,Replication / Sending Servers,Sets the maximum number of simultaneously running WAL sender processes.,,postmaster,integer,default,0,262143,,10,10,,,f
(1 row)
laetitia=# \o query_out.csv
laetitia=# \g
laetitia=# \! cat query_out.csv
name,setting,unit,category,short_desc,extra_desc,context,vartype,source,min_val,max_val,enumvals,boot_val,reset_val,sourcefile,sourceline,pending_restart
max_wal_senders,10,,Replication / Sending Servers,Sets the maximum number of simultaneously running WAL sender processes.,,postmaster,integer,default,0,262143,,10,10,,,f
laetitia=# \! cat query.sql
select * from test;
laetitia=# \i query.sql
laetitia=# \! cat query_out.csv
name,setting,unit,category,short_desc,extra_desc,context,vartype,source,min_val,max_val,enumvals,boot_val,reset_val,sourcefile,sourceline,pending_restart
max_wal_senders,10,,Replication / Sending Servers,Sets the maximum number of simultaneously running WAL sender processes.,,postmaster,integer,default,0,262143,,10,10,,,f
id,value
1,Use
2,your
3,fear...
4,it
5,can
6,take
7,you
8,to
9,the
10,place
11,where
12,you
13,store
14,your
15,courage.
laetitia=# select count(*) from pg_stat_activity where state = 'idle';
count
-------
0
(1 row)
laetitia=# \watch 1
Wed Jul 14 11:59:56 2021 (every 1s)
count
-------
0
(1 row)
Wed Jul 14 11:59:57 2021 (every 1s)
count
-------
0
(1 row)
laetitia=# select sum(setting) from pg_settings
laetitia-# where name ~ 'buffer';
ERROR: function sum(text) does not exist
LINE 1: select sum(setting) from pg_settings where name ~ 'buffer';
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
laetitia=# select setting from pg_settings
laetitia-# where name ~ 'buffer';
setting
---------
16384
1024
512
(3 rows)
laetitia=# \gdesc
Column | Type
---------+------
setting | text
(1 row)
laetitia=# select sum(setting::integer) from pg_settings
laetitia-# where name ~ 'buffer';
sum
-------
17920
(1 row)
laetitia=# begin;
BEGIN
laetitia=*# \dt test
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | laetitia
(1 row)
laetitia=*# select 'alter table ' || tablename
|| ' owner to test;'
laetitia-*# from pg_tables
laetitia-*# where tableowner = 'laetitia';
?column?
---------------------------------
alter table test owner to test;
(1 row)
laetitia=*# \gexec
ALTER TABLE
laetitia=*# \dt test
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | test | table | test
(1 row)
laetitia=*# rollback;
ROLLBACK